package com.beordie.dao.impl;

import com.beordie.dao.IStudentDao;
import com.beordie.model.Student;
import com.beordie.utils.DBUtils;
import com.beordie.utils.PageUtils;

import javax.print.attribute.standard.MediaSize;
import javax.swing.plaf.SliderUI;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * @Classname StudentDaoImpl
 * @Description TODO
 * @Date 2021/6/8 12:44
 * @Created 30500
 */
public class StudentDaoImpl extends DBUtils implements IStudentDao {
    @Override
    public List<Student> getAllStudents(String stuName, String stuNo, int sex, PageUtils pageUtils) {
        // 填充查询语句
        StringBuffer sql = new StringBuffer("select * from student where state!=4 ");
        List params = new ArrayList<>();

        if (stuName != null && stuName.length() > 0) {
            sql.append(" and stuname like ? ");
            params.add("%" + stuName + "%");
        }
        if (stuNo != null && stuNo.length() > 0) {
            sql.append(" and stuno=? ");
            params.add(stuNo);
        }
        if (sex != -1) {
            sql.append(" and sex=? ");
            params.add(sex);
        }
        sql.append(" limit ?,?");

        params.add((pageUtils.getPageIndex() - 1) * pageUtils.getPageSize());
        params.add(pageUtils.getPageSize());
        resultSet = super.query(String.valueOf(sql), params);
        List<Student> list = new ArrayList<>();

        // 设置数据库返回的数据填充到实体类
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    Student student = new Student();
                    student.setAddress(resultSet.getString("address"));
                    student.setEmail(resultSet.getString("email"));
                    student.setStuId(resultSet.getInt("stuid"));
                    student.setStuNo(resultSet.getString("stuno"));
                    student.setStuName(resultSet.getString("stuname"));
                    student.setSex(resultSet.getInt("sex"));
                    student.setPhone(resultSet.getString("phone"));
                    student.setRegistered(resultSet.getString("registered"));
                    student.setProfession(resultSet.getString("profession"));
                    student.setIdNumber(resultSet.getString("idnumber"));
                    student.setPolitics(resultSet.getString("politics"));
                    student.setRedDate(resultSet.getDate("regdate"));
                    student.setState(resultSet.getInt("state"));
                    student.setIntroduction(resultSet.getString("introduction"));
                    student.setgId(resultSet.getInt("gid"));
                    list.add(student);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                super.close();
            }
        }

        return list;
    }

    /**
     * @description 根据模糊查询返回其数据条数
     * @author 30500
     * @date 2021/6/9 14:29
     * @type [java.lang.String, java.lang.String, int]
     * @return int
     */
    public int getCount(String stuName, String stuNo, int sex) {
        // 填充查询语句
        StringBuffer sql = new StringBuffer("select count(*) from student where state!=4 ");
        List params = new ArrayList<>();
        if (stuName != null && stuName.length() > 0) {
            sql.append(" and stuname like ? ");
            params.add("%" + stuName + "%");
        }
        if (stuNo != null && stuNo.length() > 0) {
            sql.append(" and stuno=? ");
            params.add(stuNo);
        }
        if (sex != -1) {
            sql.append(" and sex=? ");
            params.add(sex);
        }

        ResultSet result = super.query(String.valueOf(sql), params);
        int total = 0;
        // 设置数据库返回的数据填充到实体类
        if (result != null) {
            try {
                while (result.next()) {
                    total = result.getInt(1);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                super.close();
            }
        }
        // 返回查询数据的长度
        return total;
    }

    @Override
    public Student getByStuId(int stuId) {
        String sql = "select * from student where stuid=?";
        List params = new ArrayList();
        params.add(stuId);
        Student student = null;
        resultSet = super.query(sql, params);
        // 设置数据库返回的数据填充到实体类
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    student = new Student();
                    student.setAddress(resultSet.getString("address"));
                    student.setEmail(resultSet.getString("email"));
                    student.setStuId(resultSet.getInt("stuid"));
                    student.setStuNo(resultSet.getString("stuno"));
                    student.setStuName(resultSet.getString("stuname"));
                    student.setSex(resultSet.getInt("sex"));
                    student.setPhone(resultSet.getString("phone"));
                    student.setRegistered(resultSet.getString("registered"));
                    student.setProfession(resultSet.getString("profession"));
                    student.setIdNumber(resultSet.getString("idnumber"));
                    student.setPolitics(resultSet.getString("politics"));
                    student.setRedDate(resultSet.getDate("regdate"));
                    student.setState(resultSet.getInt("state"));
                    student.setIntroduction(resultSet.getString("introduction"));
                    student.setgId(resultSet.getInt("gid"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                super.close();
            }
        }
        return student;
    }

    @Override
    public int delByStuId(int stuId) {
        String sql = "update student set state=4 where stuid=?";
        List params = new ArrayList();
        params.add(stuId);
        int result = super.update(sql, params);
        return result;
    }

    @Override
    public int updateInfo(Student student) {
        String sql = "update student set stuname=?,stuno=?,sex=?,phone=?,email=?," +
                "registered=?,address=?,profession=?,idnumber=?,politics=?,regdate=?," +
                "state=?,introduction=?,gid=? where stuid=?";
        List<Object> params = new ArrayList<>();
        // 填充传输的数据
        params.add(student.getStuName());
        params.add(student.getStuNo());
        params.add(student.getSex());
        params.add(student.getPhone());
        params.add(student.getEmail());
        params.add(student.getRegistered());
        params.add(student.getAddress());
        params.add(student.getProfession());
        params.add(student.getIdNumber());
        params.add(student.getPolitics());
        params.add(student.getRedDate());
        params.add(student.getState());
        params.add(student.getIntroduction());
        params.add(student.getgId());
        params.add(student.getStuId());
        // 接收返回的结果
        int result = super.update(sql, params);
        return result;
    }

    @Override
    public int addAllInfo(Student student) {
        String sql = "insert into student (stuname,stuno,sex,phone,email,registered,address,profession,idnumber,politics,regdate," +
                "state,introduction,gid) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        List<Object> params = new ArrayList<>();
        // 填充传输的数据
        params.add(student.getStuName());
        params.add(student.getStuNo());
        params.add(student.getSex());
        params.add(student.getPhone());
        params.add(student.getEmail());
        params.add(student.getRegistered());
        params.add(student.getAddress());
        params.add(student.getProfession());
        params.add(student.getIdNumber());
        params.add(student.getPolitics());
        params.add(student.getRedDate());
        params.add(student.getState());
        params.add(student.getIntroduction());
        params.add(student.getgId());

        // 接收返回的结果
        int result = super.update(sql, params);
        return result;
    }
}
